Release 10.1A: OpenEdge Data Management:
DataServer for Microsoft SQL Server

Loading result sets into temp-tables

Enhancements implemented through changes to the RUN STORED-PROC statement allow you to retrieve result sets from a foreign data source and load each result set, for which a temp-table handle is defined, into its own temp-table. The LOAD_RESULT-INTO function enables data retrieved to be loaded into temp-tables where the data can then be manipulated, employing all characteristics inherent to temp-tables. The capability to load result sets into temp-tables is not limited by the parsing requirements associated with the proc-text-buffer nor the database dependencies associated with views.

Temp-tables can provide data management capabilities associated with the 4GL directly to the result sets of a stored procedure, but completely independent of the foreign data source from which it was populated and/or derived. Temporary tables are effectively database tables in which Progress stores data temporarily. Because temp-tables have the same support features that actual OpenEdge databases use, you can take advantage of almost all the OpenEdge database features that do not require data persistence and multi-user access. For example, you can define indexes for fields in the temp-table. For more information about temp-tables, see the OpenEdge Development: Progress 4GL Handbook.

Example 3–13, introduces how to use the RUN STORED-PROC statement with the LOAD-RESULT-INTO phrase with a single dynamic temp-table. It highlights the coding techniques discussed in the "Run Stored-Proc statement execution using the send-sql-statement option" section and introduces the dynamic temp-table topic further discussed in this section.

DEFINE VARIABLE tt1 AS HANDLE. DEFINE VARIABLE q AS HANDLE. DEFINE VARIABLE bh AS HANDLE. DEFINE VARIABLE bBrowse AS HANDLE NO-UNDO. DEFINE FRAME BrowseFrame WITH SIZE 80 BY 10. CREATE TEMP-TABLE tt1. RUN STORED-PROC send-sql-statement LOAD-RESULT-INTO tt1 (“SELECT * from CUSTOMER”). bh = tt1:DEFAULT-BUFFER-HANDLE. CREATE QUERY q. q:SET-BUFFERS (bh). DISPLAY tt1:name. q:QUERY-PREPARE(“for each” + tt1:name). q:QUERY-OPEN. CREATE BROWSE hBrowse      ASSIGN ROW = 1 COL = 1      WIDTH = 79 DOWN = 10      FRAME = FRAME BrowseFrame:HANDLE      QUERY = q      SENSITIVE = YES      SEPARATORS = YES      ROW-MARKERS = NO      VISIBLE = YES. hBrowse:ADD-COLUMNS-FROM(bh). ENABLE ALL WITH FRAME BrowseFrame. WAIT-FOR CLOSE OF CURRENT-WINDOW.

Example 3–13: The RUN STORED-PROC statement with the LOAD-RESULT-INTO phrase

Getting started

If you are using static temp-tables, you must define the temp-table layout in your program to accommodate a specific stored procedure result set before attempting to populate these tables. Once this prerequisite task is done, however, temp-tables can also be automatically populated, offering a potential performance gain in most instances.

Unlike the proc-text-buffer technique, you do not have to parse the strings from the proc-text-buffer pseudo table buffer where each row is a character string. Similarly, you do not need to perform any administration to maintain views in the foreign data source or their definitions in the schema holder. For more details about planning your temp-table layout, see the "Creating a temp-table layout plan" section.

Employing additional enhancements

The temp-table technique offers even greater programming benefits as it extends both send-sql-statements and stored procedures through the result processing techniques previously described in this chapter. For example, by mapping the PROGRESS_RECID to the ROWID field in temp- tables, you can easily support KEY definitions required by the Progress DataSet (commonly referred to as a ProDataSets) to ensure that your data mappings between the foreign data source and the temp-table are accurate. Accurate data mappings are essential for sending data back from the temp-table to the data source. Non-ROWID key definitions can also be described with a unique key.

ProDataSets functionality is based on one or more temp-tables that share and extend basic temp-table functionality. For more information about ROWID field, see the "ROWID Support" section. For in depth discussion of temp-tables and more information about ProDataSets, see OpenEdge Development: ProDataSets.

Table 3–3 highlights additional language elements you can use with the stored procedure and the send-sql-statement language to use ROWID.

Table 3–3: Returning result sets and loading the data into temp-tables  

Progress 4GL	Description
RUN STORED–PROCEDURE statement	Executes the stored procedure or send-sql- statement and tells Progress that the stored procedure has ended.
LOAD-RESULT-INTO phrase	Allows data from a result set that is returned for a foreign data source either through a stored procedure or a send-sql- statement to be put into one or more temp-tables. Static, unprepared dynamic, and prepared dynamic temp-tables are supported. Pass as many temp-tables as result set instances are returned by SQL statements in your Send-SQL or RUN STORED-PROC definition. Note: When using SQL statement(s) through a send-sql-statement or stored procedure to load result sets into temp-tables, RUN STORED-PROC carries an implicit RUN CLOSE-PROC statement. (The stored procedure’s output parameters are available after the RUN STORED-PROC executes and closes the procedure.)
PROC-STATUS phrase	Reads the return value (optional).

Note: To optimize enhanced stored procedure calls, it is recommended that you use firehose cursor functionality. Use of firehose cursors provided through the connection pooling functionality supports the fastest way to read and return a result set. For details about connection pooling and firehose cursors, see the Connection Pooling section of Chapter 4, " Additional Features to Enhance DataServer Performance.".

Creating a temp-table layout plan

When using static or prepared dynamic temp-tables, you must define the temp-table layout in your application program to accommodate specific result sets before you attempt to populate the temp-tables with data. If a SQL statement retrieves more than one results set, you must define multiple temp-tables to be able to retrieve all the data. Therefore, the success of this approach depends to a large extent on your:

Understanding of the specific data your foreign data source is providing you through a given stored procedure.
Ability to correctly define temp-tables.

The following types of temp-tables can support results sets:

Static — A temp-table whose schema is defined at compile time.
Dynamic — A temp-table whose schema is defined at run time. There are two types of dynamic temp-tables: dynamic-prepared and dynamic-unprepared.

Keep in mind that you can pass handles of temp-tables that contain a mixed array. A mixed array is one in which some of the temp-table handle elements can be static while others can be dynamic.

Table 3–4 identifies the temp-table options for which you can plan and the requirements you must fulfill for each option.

Using a temp-table handle with an unprepared dynamic temp-table

When a temp-table handle points to an unprepared dynamic temp-table, the MS SQL Server DataServer defines the temp-table schema in the form of the result sets record structure which is passed back to the DataServer from the foreign data source. The data types defined for the temp-table schema are determined based on the default data type mapping that exists between the SQL data type and its equivalent Progress default data type. Once the temp-table schema is dynamically established by the DataServer, the result set begins to populate it.

Recognize that there is the possibility of a small performance price to be paid when you build dynamic temp-tables. However, considering the database independence that this technique affords over building static temp-tables, you might consider the price of dynamically built temp-tables to be a small, reasonable one.

Table 3–4: Options to plan the temp-table layout for result sets

To return a result set to this type of temp-table...	Then the layout definition is...
Static Dynamic-prepared state	Defined by you; you must base the layout on the expected fields to be returned and each of these fields’ data types so that the first field defined in the temp-table corresponds to the first column of the result set. This column matching and data type matching must be repeated successfully for each temp-table and its corresponding result set.
Dynamic - unprepared state	Not defined by you; the schema of the temp-table is based on the result-set schema and a mapping of default Progress data types for each SQL type. For more details, see the "Details about a Dynamic temp-table in an unprepared state" section.

Note: Once the data is loaded into the temp-table, any updates made to the records in the temp-table are not propagated back to the foreign database. Result sets are available through temp-tables for the purpose of obtaining a snapshot of the data. For example, you can use this technique to populate a browser from a temp-table. You must re-read the record using the proper lock mechanism to actually update the record.

Details about a Dynamic temp-table in an unprepared state

A dynamic temp-table is considered to be in an unprepared state after the first definitional method is called until the temp-table is prepared. If a clear dynamic temp-table handle is passed, the DataServer populates the temp-table schema based on the result-set schema and prepares the temp-table. A clear dynamic temp-table is a table that is in an unprepared state where definitional methods have not yet been called. The DataServer then executes the temp-table handle: ADD-NEW-FIELD (field name, data type) internally for each one of the columns of the result set. If a column in the result set from the foreign schema does not have a name (for example, an array element field or a SQL-derived field), the DataServer assigns a name to it based on the column position on the row.

For example, if you run the following statement, then the temp-table contains columns:
column 1, cust_num, name and column 4:

/* */ SELECT “mytag,” cust_num, name, (salary * 0.10) FROM <anytablename>

The data type associated with each column follows the mapping rules that exist between Progress and the foreign data source’s data types. For more information about data types and default mapping, see Chapter 2, " Initial Programming Considerations."

Note: Since a stored procedure can return multiple result sets, the DataServer prepares the temp-table as “result<n>” where <n> is the result-set sequence number that corresponds to its element position in the temp table handle array, starting with 1. Therefore, if there are 3 result sets and 3 clear dynamic temp-tables are passed, the temp-tables are called result1, result2, and result3.

Note the following error conditions as they specifically apply to a dynamic temp-table:

If an error occurs during the schema population or during the prepare of a temp-table, the DataServer raises an error condition.
The dynamic temp-table must be either already prepared or clear with no defined fields in it. If fields have already been added to a dynamic temp-table before the RUN STORED-PROC statement is executed and the temp-table is not prepared, the DataSever raises an error condition due to the invalid state of the temp-table.

Details about a Dynamic temp-table in a prepared state

The following example, Example 3–14, shows multiple dynamic temp-tables in a prepared state. The temp tables, tt1 and tt2, are prepared in the code just before the RUN STORED-PROC statement is called and the temp-tables are passed. The temp-table definition is considered prepared because of the explicit inclusion of each field name and associated data type.

/* example - Multiple dynamic temp-tables in a prepared state*/ DEFINE VAR tt1 AS handle. DEFINE VAR tt2 AS handle. DEFINE VAR tt-array AS handle extent 2. CREATE TEMP-TABLE tt1. tt1:ADD-NEW-FIELD(“cust-num”, “integer”). tt1:ADD-NEW-FIELD(“name”, “char”). /*no more fields will be added */ tt1:TEMP-TABLE-PREPARE (“ordx1”). CREATE TEMP-TABLE tt2. tt2:ADD-NEW-FIELD(“order-num”, “integer”). tt2:ADD-NEW-FIELD(“order-date”, “date”). /*no more fields will be added */ tt2:TEMP-TABLE-PREPARE (“ordx2”). Assign tt-array[1]=tt1        tt-array[2]=tt2. RUN STORED-PROC myproc LOAD-RESULT-INTO tt-array (INPUT 10, INPUT “order”).

Example 3–14: Multiple temp-tables in a prepared state

In the previous dynamic temp-table example, note that the temp-table definitions must match the result set that the send-sql- statement returns. For example, the number of fields and the data type of each field in the result must have a corresponding field defined in the temp-table that will receive the result set. No mapping exists between the result set and the temp-table. Therefore, the first field defined in the temp-table corresponds to the first column of the result set and so forth.

Additional temp-table examples

This section presents more examples that show various techniques to code temp-tables.

Consider Example 3–15 which follows this initial explanation; the stored procedure myProc is comprised of two fields—one is an integer and the other is a character, and a result set.

SELECT “myProc,” cust_num, cust_name FROM <anytablename>

Example 3–15: Temp-table code technique

This next example, Example 3–16, shows the basics of executing a call to a stored procedure using the LOAD-RESULT-INTO functionality. Note that in this example, PROC-STATUS function is not specified. Therefore, there is no possibility of a return value.

/* Calling a stored procedure, using the LOAD-RESULT-INTO phrase*/ DEF VAR ttHndl AS HANDLE DEF TEMP-TABLE ttl FIELD f1 AS INT. FIELD f2 AS CHAR. DEF VAR res AS INT. ttHndl=TEMP-TABLE tt:HANDLE. RUN STORED-PROC myProc LOAD-RESULT-INTO  ttHndl (INPUT 1, OUTPUT res).

Example 3–16: Basic execution of a stored procedure using LOAD-RESULT-INTO functionality

Example 3–17 shows the basics of using an existing dynamic temp-table without the TEMP-TABLE-PREPARE () method. In this instance, send-sql- statement is used rather than a predefined stored proc. In contrast, the third example code that appears later in this section shows the same approach, but explicitly defines the existing dynamic temp-table with the TEMP-TABLE-PREPARE() method.

/* Calling a stored procedure, using an existing temp-table without temp table prepare*/ DEF VAR ttHndl AS HANDLE. CREATE TEMP-TABLE ttHndl. RUN STORED-PROC send-sql-statement  LOAD-RESULT-INTO ttHndl  (“select * from customer”).

Example 3–17: Using an existing dynamic temp-table without the TEMP-TABLE-PREPARE ( ) method

Example 3–18 shows the basics of using an existing dynamic temp-table with the TEMP-TABLE-PREPARE() method.

/* Calling a stored procedure, using an existing temp-table with temp table prepare*/ DEF VAR ttHndl AS HANDLE CREATE TEMP-TABLE tt1Hndl. ttHndl:ADD-NEW-FIELD(“custNum”,”integer”). ttHndl:ADD-NEW-FIELD(“name”,”char”). ttHndl:TEMP-TABLE-PREPARE(“ordX”). RUN STORED-PROC send-sql-statement  LOAD-RESULT-INTO ttHndl (“select  custNum,name from myDB.customer”).

Example 3–18: Using an existing dynamic temp-table with the TEMP-TABLE-PREPARE ( ) method

Note these points as they relate the Example 3–18:

As a prerequisite for creating the code shown in the previous example, the developer would need to define the schema for the table.
Once the temp-table schema begins preparation from the clear state, the temp-table must be defined to the exact specifications for the result sets as generated by the RUN STORED-PROC statement or send-sql- statement. Otherwise, the RUN STORED-PROC will end in failure. Also, note the TEMP-TABLE-PREPARE must be called at the completion of the temp-table definition associated with the stored procedure results.

Example 3–19 shows passing the handles of empty dynamic temp-tables to enable a MS SQL Server DataServer to create schema based on the result-set schema.

/*Calling a stored procedure, and using the EXTENT phrase to pass handles of empty to enable the MS SQL DataServer to create schema holder definitions based on each temp-table’s result-set schema. */ DEF VAR ttHdl AS HANDLE EXTENT 2. DEF VAR ttcust AS HANDLE. DEF VAR ttord AS HANDLE. CREATE TEMP-TABLE ttcust. CREATE TEMP-TABLE ttord. ttHd1[1]=ttcust. ttHd1[2]=ttord. RUN STORED-PROC send-sql-statement  LOAD-RESULT-INTO ttHd1  (“Select * from cust; select * from order”).

Example 3–19: Calling a stored procedure and using the EXTENT phrase

Example 3–20 shows how to use the enhanced stored procedure syntax with a single static temp-table and the send-sql-statement.

/* Calling an enhance stored procedure with a single temp table and the send-sql-statement*/ define variable tt-handle as handle. define TEMP-TABLE tt1 Field f1 as int Field f2 as char. tt-handle=temp-table tt1:handle. RUN STORED-PROC send-sql-statement LOAD-RESULT-INTO tt-handle (“Select cust_num,name from customer”).

Example 3–20: Calling an enhanced stored procedure with a single temp table and the send-sql-statement

Example 3–21 shows the use of a PROC-STATUS phrase. The PROC-STATUS phrase must be defined as part of the RUN-STORED-PROC statement because of the implicit CLOSE STORED-PROC that is associated with the LOAD-RESULT-INTO phrase.

/* Sixth example - Example of the implicit close stored-proc and use of LOAD-RESULT-INTO */ DEFINE VAR stat AS integer. DEFINE VARIABLE ttHndl AS HANDLE. CREATE temp-table ttHndl. RUN STORED-PROCEDURE pcust LOAD-RESULT-INTO ttHndl stat=PROC-STATUS (20, output 0, output 0). DISPLAY stat.

Example 3–21: Use of a PROC-STATUS phrase

In Example 3–21, note that the PROC-STATUS phrase does not need a PROC-HANDLE phrase because it is retrieved using the RUN STORED-PROC statement and not after this statement’s execution as it typically is used.